Core와 ORM 방식으로 행 조회하기

이번 챕터에서는 SQLAlchemy에서 가장 자주 쓰이는 Select에 대해서 다룹니다.

select() 를 통한 SQL 표현식 구성

select() 생성자는 insert() 생성자와 같은 방식으로 쿼리문을 만들 수 있습니다.

>>> from sqlalchemy import select
>>> stmt = select(user_table).where(user_table.c.name == 'spongebob')
>>> print(stmt)
"""
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = :name_1
"""

마찬가지로 쿼리문을 실행시키 위해 같은 레벨의 SQL 생성자들(select, insert, update, create등)처럼 Connection.execute() 메서드에 쿼리를 넣어 실행시킬 수 있습니다.

>>> with engine.connect() as conn:
...     for row in conn.execute(stmt):
...         print(row)
(1, 'spongebob', 'Spongebob Squarepants')

한편 ORM을 사용해 select 쿼리문을 실행시키고 싶을 때는 Session.exeucte()를 사용해야합니다. 결과는 방금 전의 예제와 마찬가지로 Row객체를 반환하지만 이 객체는 이전의 튜토리얼, 4. 데이터베이스 메타데이터로 작업하기 (opens new window)에서 정의했던 User객체를 포함하고 있습니다.

>>> stmt = select(User).where(User.name == 'spongebob')
# User 객체의 인스턴스 안에 있는 각 row 들을 출력
>>> with Session(engine) as session:
...     for row in session.execute(stmt):
...         print(row)
(User(id=1, name='spongebob', fullname='Spongebob Squarepants'),)

FROM절과 컬럼 세팅하기

select()함수는 위치인자로 Column이나 Table등을 포함한 다양한 객체들을 받을 수 있습니다. 이러한 인자 값들은 select()함수의 반환 값, 즉 SQL쿼리문으로 표현 될 수있고 FROM절을 세팅해주기도 합니다.

>>> print(select(user_table))
"""
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
"""

각각의 컬럼들을 Core를 이용해 조회하려면 Table.c접근자를 통해 Column객체에 접근하면 됩니다.

>>> print(select(user_table.c.name, user_table.c.fullname))
"""
SELECT user_account.name, user_account.fullname
FROM user_account
"""

ORM 엔터티 및 열 조회

SQL 쿼리를 SqlAlchemy에서 구현할 때 테이블이나 컬럼을 표현하기 위해 User객체같은 ORM 엔터티나, User.name과 같이 컬럼이 매핑된 속성(어트리뷰트)을 사용할 수 있습니다. 아래의 예제는 User엔터티를 조회하는 예제이지만 사실은 user_table 를 사용했을 때와 결과가 동일합니다.

>>> print(select(User))
"""
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
"""

위의 예제의 쿼리를 ORM Session.exeucte()를 통해 똑같이 실행 할 수 있는데, 이때는 User엔터티를 조회하는 것과 user_info를 조회하는 것에 차이가 있습니다. user_info를 조회하든 User엔티티를 조회 하든 둘 다Row객체가 반환되지만 User엔터티를 조회 할 경우에는 User인스턴스를 포함한 Row 객체가 반환됩니다.

여기서 user_table과 User는 이전 챕터에서 만들어졌는데, user_table은 Table 객체이고 User는 Base 객체를 상속받아Table객체를 포함하고 있는 엔터티입니다.

>>> with Session(engine) as session:
...     row = session.execute(select(User)).first()
...     print(row)
(User(id=1, name='spongebob',fullname='Spongebob Squarepants'),)

한편 객체 속성(class-bound attributes)을 사용해 원하는 컬럼들을 조회할 수도 있습니다.

>>> print(select(User.name, User.fullname))
"""
SELECT user_account.name, user_account.fullname
FROM user_account
"""

객체 속성을 Session.execute()을 이용해 조회 할 경우에는 인자로 보내진 객체 속성의 값(컬럼 값)이 아래와 같이 반환됩니다.

>>> with Session(engine) as session:
...	row = session.execute(select(User.name, User.fullname)).first()
...	print(row)
('spongebob', 'Spongebob Squarepants')

이러한 방법들은 믹스인 방법으로 섞어서 사용할 수도 있습니다.

>>> session.execute(
...     select(User.name, Address).
...     where(User.id==Address.user_id).
...     order_by(Address.id)
... ).all()
[('spongebob', Address(id=1, email_address='spongebob@sqlalchemy.org')),
('sandy', Address(id=2, email_address='sandy@sqlalchemy.org')),
('sandy', Address(id=3, email_address='sandy@squirrelpower.org'))]

라벨링된 SQL 표현식 조회하기

SELECT name AS username FROM user_account 쿼리를 실행 할 경우 아래와 같은 결과가 나옵니다.

username
patrick
sandy
spongebob

여기서 우리는 name컬럼의 이름을 username으로 라벨링 해줬기 때문에 상단 컬럼에 username 이 뜨는 건데요. 이러한 기능을 SQLAlchemy의 ColumnElement.label()함수를 이용해 동일하게 구현할 수 있습니다.

>>> from sqlalchemy import func, cast
>>> stmt = (
...     select(
...         ("Username: " + user_table.c.name).label("username"),  # 이렇게 라벨링 합니다.
...     ).order_by(user_table.c.name)
... )
>>> with engine.connect() as conn:
...     for row in conn.execute(stmt):
...         print(f"{row.username}")  # 라벨링한 부분은 이렇게 접근할 수 있습니다.
Username: patrick
Username: sandy
Username: spongebob

문자열 컬럼 조회하기

보통 Select객체나 select() 생성자를 이용 해 컬럼을 조회하는 경우가 많지만 가끔은 임의로 문자열과 함께 컬럼을 조회 해야하는 경우가 있습니다. 이번 섹션에서는 이러한 스트링 데이터를 조회 하는 방법에 다룹니다.

text() 생성자는 이전 챕터 3. 트랜잭션 및 데이터베이스API 작업 (opens new window)에서 한 번 소개가 되었는데요, 이 text()생성자안에 SELECT구문을 곧바로 넣어 사용할 수도 있었습니다.

한편 우리는 SELECT 'some_phrase', name FROM user_account 와 같은 쿼리를 실행시키고 싶다고 생각해봅시다. 이 때, some_phrase은 문자열이기 때문에 꼭 큰 따옴표나 작은 따옴표로 감싸줘야합니다. 그리고 그 결과, 출력물에도 어쩔 수 없이 작은 따옴표가 전부 붙어서 나옵니다.

>>> from sqlalchemy import text
>>> stmt = (
...     select(
...         text("'some phrase'"), user_table.c.name
...     ).order_by(user_table.c.name)
... )
>>> with engine.connect() as conn:
...     print(conn.execute(stmt).all())
[('some phrase', 'patrick'), ('some phrase', 'sandy'), ('some phrase', 'spongebob')]

그래서 보통은 text()보다 literal_column()을 사용해 작은 따옴표가 결과물에 붙어져서 나오는 문제를 해결할 수 있습니다.
여기에서 text와 literal_column()은 거의 비슷하지만 literal_column()은 명시적으로 컬럼을 의미하고, 서브쿼리나 다른 SQL 표현식에서 쓰일 수 있게 라벨링도 할 수 있습니다.

>>> from sqlalchemy import literal_column
>>> stmt = (
...     select(
...         literal_column("'some phrase'").label("p"), user_table.c.name
...     ).order_by(user_table.c.name)
... )
>>> with engine.connect() as conn:
...     for row in conn.execute(stmt):
...         print(f"{row.p}, {row.name}")
some phrase, patrick
some phrase, sandy
some phrase, spongebob

WHERE절

SQLAlchemy를 사용하면 Python 연산자를 사용하여 name = 'thead'또는user_id > 10` 인 데이터만 출력하는 쿼리를 쉽게 작성할 수 있습니다.

>>> print(user_table.c.name == 'squidward')
user_account.name = :name_1

>>> print(address_table.c.user_id > 10)
address.user_id > :user_id_1

WHERE절을 만들기 위해 Select.where()메서드안에 인자값을 넣어 사용할 수도 있습니다.

>>> print(select(user_table).where(user_table.c.name == 'squidward'))
"""
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = :name_1
"""

WHERE 절을 통한 JOIN을 구현해야 할 때, 아래와 같이 작성 가능합니다.

>>> print(
...         select(address_table.c.email_address).
...         where(user_table.c.name == 'squidward').
...         where(address_table.c.user_id == user_table.c.id)
... )
"""
SELECT address.email_address
FROM address, user_account
WHERE user_account.name = :name_1 AND address.user_id = user_account.id
"""

# 위와 같은 표현이지만, 아래처럼 where()메서드의 파라미터로 넘기는 방식도 가능합니다.
>>> print(
        select(address_table.c.email_address).
...         where(
...             user_table.c.name == 'squidward',
...             address_table.c.user_id == user_table.c.id
...         )
... )
"""
SELECT address.email_address
FROM address, user_account
WHERE user_account.name = :name_1 AND address.user_id = user_account.id
"""

and_(), or_()등의 연결 구문도 구현가능합니다.

>>> from sqlalchemy import and_, or_
>>> print(
...     select(Address.email_address).
...         where(
...             and_(
...                 or_(User.name == 'squidward', User.name == 'sandy'),
...                 Address.user_id == User.id
...             )
...         )
...  )
"""
SELECT address.email_address
FROM address, user_account
WHERE (user_account.name = :name_1 OR user_account.name = :name_2)
AND address.user_id = user_account.id
"""

단순히 같은지, 아닌지 비교하는 경우(equality) Select.filter_by()도 자주 사용됩니다.

>>> print(
...     select(User).filter_by(name='spongebob', fullname='Spongebob Squarepants')
... )
"""
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = :name_1 AND user_account.fullname = :fullname_1
"""

FROM절과 JOIN 명시하기

앞에서 언급했지만 FROM절은 따로 명시하지 않아도 select()메서드의 인자에 넣은 컬럼들로 자동 세팅이 됩니다.

# 따로 FROM 절을 명시하지 않았지만 FROM 절이 세팅되어 출력됩니다.
>>> print(select(user_table.c.name))
"""
SELECT user_account.name
FROM user_account
"""

만약 select()의 위치 인자로 서로 다른 두 개의 테이블을 참조하는 컬럼을,(컴마)로 구분지어 넣을 수도 있습니다

>>> print(select(user_table.c.name, address_table.c.email_address))
"""
SELECT user_account.name, address.email_address
FROM user_account, address
"""

서로 다른 두 개의 테이블을 JOIN조인하고 싶다면, 이용할 수 있는 메서드가 두 가지가 있는데요, 하나는 Select.join()메서드로 명시적으로 JOIN 할 왼쪽에 들어갈 테이블, 오른쪽에 들어 갈 테이블을 직접 지정할 수 있습니다

>>> print(
...     select(user_table.c.name, address_table.c.email_address).
...     join_from(user_table, address_table)
... )
"""
SELECT user_account.name, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
"""

다른 하나는 Select.join()메서드로 오른쪽에 들어갈 테이블만 명시적으로 적어주고 나머지 테이블은 암시적으로 컬럼을 선택할 때 참조하게 합니다.

# 위와 동일한 표현이지만, 조인할 왼쪽 테이블(user_table)은 암시적으로 표현됩니다.
>>> print(
...     select(user_table.c.name, address_table.c.email_address).
...     join(address_table)
... )
"""
SELECT user_account.name, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
"""

또는 JOIN 하는 두 개의 테이블을 조금 더 명시적으로 작성하고 싶거나 FROM절에 명시적인 추가 옵션을 주고 싶다면 아래와 같이 작성 할 수도 있습니다.

>>> print(
...     select(address_table.c.email_address).
...     select_from(user_table).join(address_table)
... )
"""
SELECT address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
"""

Select.select_from()을 사용하는 또 다른 경우는 우리가 조회하고 싶은 컬럼들을 통해 암시적으로 FROM 절을 세팅할 수 없는 경우입니다. 예를 들면 일반적인 SQL 쿼리문에서 count(*)를 조회하기 위해선 SQLAlchemy의 sqlalchemy.sql.expression.func를 사용해야합니다.

>>> from sqlalchemy import func
>>> print(select(func.count('*')).select_from(user_table))
"""
SELECT count(:count_2) AS count_1
FROM user_account
"""

On절 세팅하기

그런데 뭔가 이상한게 있었죠? 사실 이 전의 예제에서 Select.select_from()이나 select.join()을 이용해 두 테이블을 JOIN할 때 암시적으로 ON절이 세팅되었답니다.
왜 자동으로 ON 절이 세팅 됐냐면, user_table객체와, address_table 객체가 ForeignKeyConstraint, 즉 외부키 제약을 갖고 있어서 자동으로 세팅이 된 것입니다.

만약에 Join의 대상인 두 개의 테이블에서 이러한 제약 key가 없을 경우 ON절을 직접 지정해야 합니다. 이러한 기능은 Select.join()나 Select.join_from()메서드에 파라미터 전달을 통해 명시적으로 ON절을 세팅할 수 있습니다.

>>> print(
...     select(address_table.c.email_address).
...     select_from(user_table).
...     join(address_table, user_table.c.id == address_table.c.user_id)
... )
"""
SELECT address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
"""

OUTER, FULL Join

SQLAlchemy에서 LEFT OUTER JOIN, FULL OUTER JOIN를 구현하려면 Select.join()과 Select.join_from()메서드의 키워드 인자로 Select.join.isouter, Select.join.full를 사용할 수 있습니다.

LEFT OUTER JOIN을 구현 한 경우,

>>> print(
...     select(user_table).join(address_table, isouter=True)
... )
"""
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account LEFT OUTER JOIN address ON user_account.id = address.user_id
"""

FULL OUTER JOIN을 구현 한 경우,

>>> print(
...     select(user_table).join(address_table, full=True)
... )
"""
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account FULL OUTER JOIN address ON user_account.id = address.user_id
"""

ORDER BY, GROUP BY, HAVING

• ORDER BY절은 SELECT절에서 조회한 행들의 순서를 설정할 수 있습니다.
• GROUP BY절은 그룹 함수로 조회된 행들을 특정한 컬럼을 기준으로 그룹을 만듭니다.
• HAVING은 GROUP BY절을 통해 생성된 그룹에 대해 조건을 겁니다.

ORDER BY

Select.order_by()를 이용해 ORDER BY기능을 구현할 수 있습니다. 이때 위치 인자 값으로 Column객체나 이와 비슷한 객체들을 받을 수 있습니다.

>>> print(select(user_table).order_by(user_table.c.name))
"""
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account ORDER BY user_account.name
"""

오름차순, 내림차순은 ColumnElement.asc(), ColumnElement.desc() 제한자를 통해 구현 할 수 있습니다. 아래 예제는 user_account.fullname컬럼 기준으로 내림 차순으로 정렬합니다.

>>> print(select(User).order_by(User.fullname.desc()))
"""
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account ORDER BY user_account.fullname DESC
"""

그룹함수 : GROUP BY, Having

SQL에서는 집계함수를 이용해 조회 된 여러개의 행들을 하나의 행으로 합칠 수도 있습니다. 집계함수의 예로 COUNT(), SUM(), AVG()등이 있습니다.

SQLAlchemy에서는 func라는 네임스페이스를 이용해 SQL함수를 제공하는데, 이 func는 SQL함수의 이름이 주어지면 Function인스턴스를 생성합니다.

아래의 예제에서는 user_account.id컬럼을 SQL COUNT()함수에 렌더링 하기 위해 count()함수를 호출합니다.

>>> from sqlalchemy import func
>>> count_fn = func.count(user_table.c.id)
>>> print(count_fn)
"""
count(user_account.id)
"""

SQL 함수에 관해서는 SQL 함수 다루기에서 더 자세히 설명되어 있습니다.

다시 설명하자면

• GROUP BY는 조회된 행들을 특정 그룹으로 나눌 때 필요한 함수입니다. 만약에 SELECT 절에서 몇 개의 컬럼을 조회 할 경우 SQL에서는 직,간접적으로 이 컬럼들이 기본키(primary key)를 기준으로 GROUP BY에 종속되도록 합니다.
• HAVING 는 GROUP BY로 만들어진 그룹들에 대해 조건을 적용 할 경우 필요합니다.(그룹에 대해 조건을 걸기 때문에 WHERE절과 비슷합니다)

SQLAlchemy에서는 Select.group_by()와 Select.having()를 이용해 GROUP BY와 HAVING을 구현 할 수 있습니다.

>>> with engine.connect() as conn:
...     result = conn.execute(
...         select(User.name, func.count(Address.id).label("count")).
...         join(Address).
...         group_by(User.name).
...         having(func.count(Address.id) > 1)
...     )
...     print(result.all())
""" 위 구문은 아래 SQL을 표현합니다.
SELECT user_account.name, count(address.id) AS count
FROM user_account JOIN address ON user_account.id = address.user_id GROUP BY user_account.name
HAVING count(address.id) > ?
[...] (1,)
"""

[('sandy', 2)]

별칭을 통해 그룹화 또는 순서 정렬하기

어떤 데이터 베이스 백엔드에서는 집계함수를 사용해 테이블을 조회 할 때 ORDER BY 절이나 GROUP BY절에 이미 명시된 집계함수를 다시 명시적으로 사용하지 않는 것이 중요합니다.

# NOT GOOD
SELECT id, COUNT(id) FROM user_account GROUP BY id ORDER BY count(id)

# CORRECT
SELECT id, COUNT(id) as cnt_id FROM user_account GROUP BY id ORDER BY cnt_id

따라서 별칭을 통해 ORDER BY 나 GROUP BY를 구현하려면 Select.order_by() 또는 Select.group_by()메서드에 인자로 사용 할 별칭을 넣어주면 됩니다.
여기에 사용된 별칭이 먼저 렌더링 되는건 아니고 컬럼절에 사용된 별칭이 먼저 렌더링 됩니다. 그리고 렌더링된 별칭이 나머지 쿼리문에서 매칭되는게 없다면 에러가 발생합니다.

>>> from sqlalchemy import func, desc
>>> # 컬럼에도 num_addresses 별칭이 들어가고 order_by에도 같은 별칭이 들어갑니다.
>>> stmt = select(
...         Address.user_id,
...         func.count(Address.id).label('num_addresses')).\
...         group_by("user_id").order_by("user_id", desc("num_addresses"))
>>> print(stmt)
"""
SELECT address.user_id, count(address.id) AS num_addresses
FROM address GROUP BY address.user_id ORDER BY address.user_id, num_addresses DESC
"""

별칭 사용하기

여러개의 테이블을 JOIN을 이용해 조회 할 경우 쿼리문에서 테이블 이름을 여러번 적어줘야 하는 경우가 많습니다. SQL에서는 이러한 문제를 테이블 명이나 서브 쿼리에 별칭(aliases)를 지어 반복되는 부분을 줄일 수 있습니다.

한편 SQLAlchemy에서는 이러한 별칭들은 Core의 FromCaluse.alias()함수를 이용해 구현 할 수 있습니다. Table객체 네임스페이스 안에 Column객체가 있어 Table.c로 컬럼명에 접근할 수 있었는데요.

print(select(user_table.c.name, user_table.c.fullname))
"""
SELECT user_account.name, user_account.fullname
FROM user_account
"""

Alias객체 네임스페이스 안에도 Column객체가 있어 Alias.c로 컬럼에 접근 가능합니다.

>>> # user_alias_1과 user_alias_2 모두 Alias객체입니다.
>>> user_alias_1 = user_table.alias(‘table1’) 
>>> user_alias_2 = user_table.alias(‘table2’)
>>> # 방금 만든 테이블 별명으로 컬럼에 접근하려면 Alias.c.컬럼명으로 접근해야합니다.
>>> print(
...     select(user_alias_1.c.name, user_alias_2.c.name).
...     join_from(user_alias_1, user_alias_2, user_alias_1.c.id > user_alias_2.c.id)
... )

"""
SELECT table1.name, table2.name AS name_1 
FROM user_account AS table1 JOIN user_account AS table2 ON table1.id > table2.id
"""

ORM 엔티티 별칭

ORM도 FromClause.alias()메서드와 비슷한 aliased()함수가 존재합니다.

이 ORM aliased()는 ORM의 기능을 유지하면서 원래 매핑된 Table객체에 내부적으로 Alias객체를 생성합니다.

여기서 Address와 User객체는 이전 챕터에서 만들어졌는데, 두 객체 모두 Base 객체를 상속받아Table객체를 포함하고 있는 엔터티입니다.

>>> user_alias_1 = user_table.alias()
>>> user_alias_2 = user_table.alias()
>>> # 예제에서는 User나 Address엔터티에 적용됩니다.  
>>> print(
...     select(User).
...     join_from(User, address_alias_1).
...     where(address_alias_1.email_address == 'patrick@aol.com').
...     join_from(User, address_alias_2).
...     where(address_alias_2.email_address == 'patrick@gmail.com')
... )
"""
SELECT user_account.id, user_account.name, user_account.fullname 
FROM user_account JOIN address AS address_1 ON user_account.id = address_1.user_id JOIN address AS address_2 ON user
"""

서브쿼리와 CTE

이 섹션에서는 일반적으로 SELECT의 FROM 절에 있는 서브 쿼리에 대해 설명합니다. 서브쿼리와 유사한 방식으로 사용되지만 추가 기능이 포함된 CTE도 다룹니다.

CTE(Common Table Expression)는 동일 쿼리 내에서 여러번 참조할 수 있게 하는 쿼리 내 임시 결과 집합입니다. 이 글 (opens new window)에 CTE에 대해 잘 설명되어 있으니, 잘 모르시겠는 분들은 참고하시면 좋습니다.

SQLAlchemy는 Subquery 개체를 Select.subquery()사용하여 서브 쿼리를 나타내고 Select.cte() 를 사용하여 CTE를 나타냅니다.

>>> subq = select(
...     func.count(address_table.c.id).label("count"),
...     address_table.c.user_id
... ).group_by(address_table.c.user_id).subquery()
>>> print(subq)
"""
SELECT count(address.id) AS count, address.user_id
FROM address GROUP BY address.user_id
"""

>>> stmt = select(
...    user_table.c.name,
...    user_table.c.fullname,
...    subq.c.count
... ).join_from(user_table, subq) # ON절은 두 개의 테이블이 이미 foreigh key로 제약이 걸려있어 자동 바인딩된다.
>>> print(stmt)
"""
SELECT user_account.name, user_account.fullname, anon_1.count
FROM user_account JOIN (SELECT count(address.id) AS count, address.user_id AS user_id
FROM address GROUP BY address.user_id) AS anon_1 ON user_account.id = anon_1.user_id
"""

계층 쿼리

SQLAlchemy에서 CTE 구문을 사용하는 방법은 서브 쿼리 구문이 사용되는 방식과 거의 동일합니다. 대신 Select.subquery() 메서드의 호출을 Select.cte()를 사용하도록 변경하여 결과 객체를 FROM 요소로 사용할 수 있습니다.

>>> subq = select(
...     func.count(address_table.c.id).label("count"),
...     address_table.c.user_id
... ).group_by(address_table.c.user_id).cte()

>>> stmt = select(
...    user_table.c.name,
...    user_table.c.fullname,
...    subq.c.count
... ).join_from(user_table, subq)

>>> print(stmt)
"""
WITH anon_1 AS
(SELECT count(address.id) AS count, address.user_id AS user_id
FROM address GROUP BY address.user_id)
 SELECT user_account.name, user_account.fullname, anon_1.count
FROM user_account JOIN anon_1 ON user_account.id = anon_1.user_id
"""

ORM 엔티티 서브쿼리, CTE

여기서는 aliased()가 Subquery, CTE서브 쿼리에 대해 동일한 작업을 수행하는 과정을 확인할 수 있습니다.

>>> subq = select(Address).where(~Address.email_address.like('%@aol.com')).subquery()
>>> address_subq = aliased(Address, subq)
>>> stmt = select(User, address_subq).join_from(User, address_subq).order_by(User.id, address_subq.id)
>>> with Session(engine) as session:
...     for user, address in session.execute(stmt):
...         print(f"{user} {address}")

""" 위 구문은 아래 쿼리를 표현합니다.
SELECT user_account.id, user_account.name, user_account.fullname,
anon_1.id AS id_1, anon_1.email_address, anon_1.user_id
FROM user_account JOIN
(SELECT address.id AS id, address.email_address AS email_address, address.user_id AS user_id
FROM address
WHERE address.email_address NOT LIKE ?) AS anon_1 ON user_account.id = anon_1.user_id
ORDER BY user_account.id, anon_1.id
[...] ('%@aol.com',)
"""

User(id=1, name='spongebob', fullname='Spongebob Squarepants') Address(id=1, email_address='spongebob@sqlalchemy.org')
User(id=2, name='sandy', fullname='Sandy Cheeks') Address(id=2, email_address='sandy@sqlalchemy.org')
User(id=2, name='sandy', fullname='Sandy Cheeks') Address(id=3, email_address='sandy@squirrelpower.org')

아래는 CTE생성자를 이용해 위와 같은 사용하는 예제입니다

>>> cte = select(Address).where(~Address.email_address.like('%@aol.com')).cte()
>>> address_cte = aliased(Address, cte)
>>> stmt = select(User, address_cte).join_from(User, address_cte).order_by(User.id, address_cte.id)
>>> with Session(engine) as session:
...     for user, address in session.execute(stmt):
...         print(f"{user} {address}")

User(id=1, name='spongebob', fullname='Spongebob Squarepants') Address(id=1, email_address='spongebob@sqlalchemy.org')
User(id=2, name='sandy', fullname='Sandy Cheeks') Address(id=2, email_address='sandy@sqlalchemy.org')
User(id=2, name='sandy', fullname='Sandy Cheeks') Address(id=3, email_address='sandy@squirrelpower.org')

스칼라 서브 쿼리, 상호연관 쿼리

스칼라 서브 쿼리에 대해 설명하기전 잠시 SQL에서 서브 쿼리에 대해 이야기 하겠습니다. 출처:바이헨 블로그 (opens new window)

• "서브쿼리"란 하나의 SQL문에 속한 SELECT문을 말하고 서브 쿼리의 바깥 쪽에 있는 SQL문을 "메인 쿼리"라고 합니다.

이때 서브쿼리의 종류는 메인 쿼리 컬럼을 참조 여부, 서브쿼리의 선언 위치, 서브 쿼리 실행 결과 ROW수에 따라 종류가 나눠집니다

• 메인 쿼리 컬럼 참조 여부에 따른 구분
  • 상호 연관 서브쿼리 : 서브쿼리가 메인 쿼리 컬럼을 참조
  • 비상호 연관 서브 쿼리: 서브쿼리가 메인 쿼리의 컬럼을 참조하지 않고 독립적으로 수행하고 메인쿼리에 정보를 전달할 목적으로 사용됩니다.
• 서브쿼리 선언 위치에 따른 구분
  • 스칼라 서브 쿼리 : SELECT 문의 컬럼자리에 오는 서브 쿼리(상호 연관 서브쿼리)
  • 인라인 뷰 : FROM절 자리에 오는 서브 쿼리 (상호 연관 서브 쿼리)
  • 중첩 서브 쿼리 : Where절 자리에 오는 서브 쿼리 (비상호 연관 서브 쿼리)
• 서브 쿼리 실행 결과 ROW수에 따른 구분
  • 단일행 서브쿼리(서브 쿼리 연산결과 ROW1개)
  • 단중행 서브쿼리(서브 쿼리 연산결과 ROW2개이상):IN, ANY, ALL, EXSITS

SQLAlchemy에서 스칼라 서브 쿼리는 ColumnElement객체의 일부인 ScalarSelect를 사용하는 방면 일반 서브 쿼리는FromClause객체에 있는 Subquery를 사용합니다.

스칼라 서브쿼리는 앞에서 설명했던 그룹 합수 (opens new window)와 같이 쓰이고는 합니다.

# Select.scalar_subquery()를 이용해 구현한 스칼라 서브 쿼리
>>> subq = select(func.count(address_table.c.id)).
...             where(user_table.c.id == address_table.c.user_id).
...             scalar_subquery()
>>> print(subq) #ScalarSelect객체
"""
(SELECT count(address.id) AS count_1
FROM address, user_account
WHERE user_account.id = address.user_id)
"""

스칼라 서브 쿼리가 user_account와 address를 FROM절에서 렌더링하지만 메인쿼리에 있는 user_account테이블이 있어서 스칼라 서브 쿼리에서는 user_account 테이블을 렌더링하지 않습니다.

>>> stmt = select(user_table.c.name, subq.label("address_count"))
>>> print(stmt)
"""
SELECT user_account.name, (SELECT count(address.id) AS count_1
FROM address
WHERE user_account.id = address.user_id) AS address_count
FROM user_account
"""

한편 상호 연관 쿼리를 작성 할 때 테이블 간의 연결이 모호해질 수도 있습니다.

튜토리얼에 나와있는 상호 연관쿼리 예제는 제가 이해하지 못했습니다. 잘 아시는분은 이문서에 기여 부탁드립니다.

UNION, UNION ALL 연산자들

SQL에서는 UNION, UNION ALL등으로 두 개의 SELECT문을 합치는 것을 의미합니다.
아래와 같이 쿼리문을 실행 할 수도 있습니다.

SELECT id FROM user_account
union 
SELECT email_address FROM address

그 외에도 집합 연산인 INTERSECT(교집합), EXCEPT(차집합)도 SQL에서 지원합니다.
SQLAlchemy에서 Select 객체에 대하여 union(), intersect(), except_() 혹은 union_all(), intersect_all(), except_all()을 지원합니다.

이러한 함수들의 반환 값은 CompoundSelect인데 Select와 비슷하게 쓰일 수 있는 객체이지만 더 적은 메서드를 갖고 있습니다.
union_all()의 반환값 CompoundSelect객체는 Connection.execute()로 실행될 수 있습니다.

>>> from sqlalchemy import union_all
>>> stmt1 = select(user_table).where(user_table.c.name == 'sandy')
>>> stmt2 = select(user_table).where(user_table.c.name == 'spongebob')
>>> u = union_all(stmt1, stmt2) #u는 CompoundSelect 객체입니다.
>>> with engine.connect() as conn:
...     result = conn.execute(u)
...     print(result.all())

[(2, 'sandy', 'Sandy Cheeks'), (1, 'spongebob', 'Spongebob Squarepants')]

Subquery객체를 만들기 위해 Select가 SelectBase.subquery()메서드를 제공하는 것처럼 CompoundSelect객체를 서브 쿼리로 비슷한 방식으로 사용 할 수 있습니다.

>>> u_subq = u.subquery()
>>> stmt = (
...     select(u_subq.c.name, address_table.c.email_address).
...     join_from(address_table, u_subq).
...     order_by(u_subq.c.name, address_table.c.email_address)
... )
>>> with engine.connect() as conn:
...     result = conn.execute(stmt)
...     print(result.all())

[('sandy', 'sandy@sqlalchemy.org'), ('sandy','sandy@squirrelpower.org'), 
('spongebob', 'spongebob@sqlalchemy.org')]

EXISTS 서브쿼리들

SQLAlchemy는 SelectBase.exists()메서드를 통해 Exists객체를 만들어 EXISTS 구문을 구현합니다.

>>> # subq는 Exists객체입니다.
>>> subq = (
...     select(func.count(address_table.c.id)).
...     where(user_table.c.id == address_table.c.user_id).
...     group_by(address_table.c.user_id).
...     having(func.count(address_table.c.id) > 1)
... ).exists()
>>> print(subq)
"""
EXISTS (SELECT count(address.id) AS count_1 
FROM address, user_account 
WHERE user_account.id = address.user_id GROUP BY address.user_id 
HAVING count(address.id) > :count_2)
"""
>>> with engine.connect() as conn:
...     result = conn.execute(
...         select(user_table.c.name).where(subq)
...     )
...     print(result.all())

[('sandy',)]

한편 EXISTS 구문은 부정으로 사용되지 않는 경우가 더 많습니다.

# 이메일 주소가 없는 유저네임을 선택하는 쿼리문입니다.
# "~" 연산이 들어간 부분을 확인해보세요
>>> subq = (
...     select(address_table.c.id).
...     where(user_table.c.id == address_table.c.user_id)
... ).exists()
>>> stmt = select(user_table.c.name).where(~subq)
>>> print(stmt)
"""
SELECT user_account.id 
FROM user_account 
WHERE NOT (EXISTS (SELECT count(address.id) AS count_1 
FROM address 
WHERE user_account.id = address.user_id GROUP BY address.user_id 
HAVING count(address.id) > :count_2))
"""
>>> with engine.connect() as conn:
...     result = conn.execute(stmt)
...     print(result.all())

[('patrick',)]

SQL 함수 다뤄보기

이 섹션 앞부분의 그룹함수 :GROUP BY, HAVING에서 처음 소개된 func 객체는 새로운 Function 객체를 생성하기 위한 팩토리 역할을 합니다.
select()와 같은 구문을 사용 할때는 인자 값으로 func객체로 생성된 SQL함수를 받을 수 있습니다.

• count() : 집계함수로 행의 개수를 출력하는데 사용됩니다.

  >>> # cnt는 <class 'sqlalchemy.sql.functions.count'>타입입니다.
  >>> cnt = func.count() 
  >>> print(select(cnt).select_from(user_table))
  """
  SELECT count(*) AS count_1FROM user_account
  """
  

• lower() : 문자열 함수로 문자열을 소문자로 바꿔줍니다.

  >>> print(select(func.lower("A String With Much UPPERCASE")))
  """
  SELECT lower(:lower_2) AS lower_1
  """
  

• now() : 현재 시간과 날짜를 반환해주는 함수입니다. 이 함수는 굉장히 흔하게 사용되는 함수이기에 SQLAlchemy는 서로 다른 백엔드에서 손쉽게 렌더링 할 수 있도록 도와줍니다.

  >>> stmt = select(func.now())
  >>> with engine.connect() as conn:
  ...     result = conn.execute(stmt)
  ...     print(result.all())
  
  [(datetime.datetime(...),)]
  

다양한 데이터베이스 백엔드에서는 서로 다른 이름의 SQL함수를 갖고 있습니다.
따라서 func 는 가능한 자유롭게 어떤 이름이든 func의 네임스페이스에 접근 할 수 있도록 허용합니다. 그리고 그 이름을 자동으로 SQL 함수로 받아들여 렌더링 합니다.

>>> # crazy_function의 데이터 타입은 Function입니다.
>>> crazy_function = func.some_crazy_function(user_table.c.fullname, 17)
>>> print(select(crazy_function))
"""
SELECT some_crazy_function(user_account.name, :some_crazy_function_2) AS some_crazy_function_1
FROM user_account
"""

한편 SQLAlchemy에서는 SQL에서 일반적으로 자주 쓰이는 count, now, max , concat같은 SQL 함수를 백엔드별로 적절한 데이터 타입을 제공합니다.

>>> from sqlalchemy.dialects import postgresql
>>> print(select(func.now()).compile(dialect=postgresql.dialect()))
"""
SELECT now() AS now_1
"""

>>> from sqlalchemy.dialects import oracle
>>> print(select(func.now()).compile(dialect=oracle.dialect()))
"""
SELECT CURRENT_TIMESTAMP AS now_1 FROM DUAL
"""

Functions Have Return Types

원문의 Functions Have Return Types 부분은 제가 이해하지 못했습니다. 이에 대해 이해하신 분 있으시다면 이 부분에 기여 부탁드립니다. 감사합니다.

Built-in Functions Have Pre-Configured Return Types

원문의 Built-in Functions Have Pre-Configured Return Types 부분은 제가 이해하지 못했습니다. 이에 대해 이해하신 분 있으시다면 이 부분에 기여 부탁드립니다. 감사합니다.

윈도우 함수

윈도우 함수는 GROUP BY와 비슷한 함수이고 행간의 관계를 쉽게 정의 하기 위해 만든 함수입니다.
윈도우 함수에 대해 알고 싶으신 분들은 민지님 블로그 (opens new window)에 자세한 설명이 나와있으니 한 번 읽어보시고 아래의 내용들을 이어서 읽어주세요.

SQLAlchemy에서는, func 네임스페이스에 의해 생성된 모든 SQL 함수 중 하나로 OVER 구문을 구현하는 FunctionElement.over() 메서드가 있습니다.

윈도우 함수 중 하나로 행의 개수를 세는 row_number() 함수가 있습니다.
각 행을 사용자 이름대로 그룹을 나누고 그 안에서 이메일 주소에 번호를 매길 수 있습니다.

# FunctionElement.over.partition_by파라미터를 사용하여 
# PARTITION BY 절이 OVER 절에 렌더링되도록 했습니다.
>>> stmt = select(
...     func.row_number().over(partition_by=user_table.c.name),
...     user_table.c.name,
...     address_table.c.email_address
... ).select_from(user_table).join(address_table)
>>> with engine.connect() as conn:  
...     result = conn.execute(stmt)
...     print(result.all())
[(1, 'sandy', 'sandy@sqlalchemy.org'), 
 (2, 'sandy', 'sandy@squirrelpower.org'), 
 (1, 'spongebob', 'spongebob@sqlalchemy.org')]

FunctionElement.over.order_by를 사용하여 ORDER BY 절을 사용할 수도 있습니다.

>>> stmt = select(
...     func.count().over(order_by=user_table.c.name),
...     user_table.c.name,
...     address_table.c.email_address).select_from(user_table).join(address_table)
>>> with engine.connect() as conn:  
...     result = conn.execute(stmt)
...     print(result.all())

[(2, 'sandy', 'sandy@sqlalchemy.org'), 
 (2, 'sandy', 'sandy@squirrelpower.org'), 
 (3, 'spongebob', 'spongebob@sqlalchemy.org')]

WITHIN GROUP, FILTER등 특수한 지정자

WITHIN GORUP이라는 SQL 구문은 순서 집합 또는 가상 집합 그리고 집계함수와 함께 쓰입니다. 일반적인 순서 집합 함수는 percentile_cont() 그리고 rank()를 포함하고 있습니다. SQLAlchemy에서는 rank, dense_rank, percentile_count, percentile_disc가 구현되어 있고 각각은 FunctionElement.within_group()메서드를 갖고 있습니다.

>>> print(
...     func.unnest(
...         func.percentile_disc([0.25,0.5,0.75,1]).within_group(user_table.c.name)
...     )
... )
"""
unnest(percentile_disc(:percentile_disc_1) WITHIN GROUP (ORDER BY user_account.name))
"""

어떤 백엔드에서는 "FILTER"를 지원하는데 이는 FunctionElement.filter()메서드로 사용이 가능합니다.

>>> stmt = select(
...     func.count(address_table.c.email_address).filter(user_table.c.name == 'sandy'),
...     func.count(address_table.c.email_address).filter(user_table.c.name == 'spongebob')
... ).select_from(user_table).join(address_table)
>>> with engine.connect() as conn:  
...     result = conn.execute(stmt)
...     print(result.all())

"""
SELECT count(address.email_address) FILTER (WHERE user_account.name = ?) AS anon_1,
count(address.email_address) FILTER (WHERE user_account.name = ?) AS anon_2
FROM user_account JOIN address ON user_account.id = address.user_id
"""

('sandy', 'spongebob')
[(2, 1)]

Table-Valued Functions

원문의 Table-Valued Functions 부분은 제가 이해하지 못했습니다. 이에 대해 이해하신 분 있으시다면 이 부분에 기여 부탁드립니다. 감사합니다.

컬럼값 함수 또는 스칼라 컬럼(테이블값 함수)

Oracle과 PostgresSQL에서 지원하는 특별 문법 중 하나로 FROM절에 세팅되는 함수들이 있습니다.
PostgreSQL에서는 json_array_elements(), json_object_keys(), json_each_text(), json_each()등의 함수가 그 예입니다.

SQLAlchemy는 이러한 함수를 컬럼 값이라고 하며 Function객체에 지정자로 FunctionElement.column_valued()로 적용하여 사용할 수 있습니다.

>>> from sqlalchemy import select, func
>>> stmt = select(func.json_array_elements('["one", "two"]').column_valued("x"))
>>> print(stmt)
"""
SELECT x
FROM json_array_elements(:json_array_elements_1) AS x
"""

컬럼값 함수는 오라클에서도 아래와 같이 커스텀 SQL 함수로 사용할 수 있습니다.

>>> from sqlalchemy.dialects import oracle
>>> stmt = select(func.scalar_strings(5).column_valued("s"))
>>> print(stmt.compile(dialect=oracle.dialect()))
"""
SELECT COLUMN_VALUE s
FROM TABLE (scalar_strings(:scalar_strings_1)) s
"""